package marvin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import marvin.model.MemberVO;
import marvin.utils.OracleJDBC;

public class MemberDAO {
	private final String insertSQL
		= " INSERT INTO MEMBERV1 (MNO,USERID,PWD,NAME,GENDER) " + 
				" VALUES (MBSEQ.NEXTVAL,?,?,?,?) ";
	private final String updateSQL = " UPDATE MEMBERV1 SET PWD = ?, NAME = ? WHERE MNO = ?";
	private final String deleteSQL = " DELETE FROM MEMBERV1 WHERE MNO = ?";
	private final String selcetSQL = " SELECT MNO, USERID,NAME,REGDATE FROM MEMBERV1 ORDER BY MNO DESC ";
	private final String selectOneSQL = " select * from memberv1 where mno = ?";
//	private final String selectUidSQL = "select userid from memberv1 where userid = ?";
	
	// 회원가입 insert
	public int insertMember(MemberVO m) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		int cnt = 0;
		
		try { 
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, m.getUserid());
				ps.setString(2, m.getPwd());
				ps.setString(3, m.getName());
				ps.setString(4, m.getGender());
			cnt = ps.executeUpdate();
		}catch( Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	// 회원수정 update
	public int updateMember(MemberVO m){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, m.getPwd());
				ps.setString(2, m.getName());
				ps.setString(3, m.getMno());
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	// 회원삭제 delete
	public int deleteMember(String mno){
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1,mno );
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			OracleJDBC.closeConn(conn, ps, null);
		}
		
		return cnt;
	}
	
	// 회원 전체조회 select
	public ArrayList<MemberVO> selectMember(){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<MemberVO> list
						= new ArrayList<>();
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selcetSQL);
			rs = ps.executeQuery();
			while(rs.next()){
				MemberVO m = 
						new MemberVO(rs.getString(1),rs.getString(2), 
									null, rs.getString(3), null,rs.getString(4));
				list.add(m);
				}
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
				OracleJDBC.closeConn(conn, ps, rs);}
			
			
		return list;
	}
	
	// 회원 상세조회 selectOne
	public MemberVO selectOneMember(String mno){
		MemberVO m = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectOneSQL);
				ps.setString(1, mno); // 매개변수 설정
			rs = ps.executeQuery(); // 실행
			while(rs.next()){
				m = new MemberVO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), 
						rs.getString(5), rs.getString(6));
				}
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
				OracleJDBC.closeConn(conn, ps, rs);}
						
		
		return m;
	}
	
//	public MemberVO selectUserid(String userid){
//		Connection conn = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		String result = "";
//		MemberVO m = null;
//		try{
//			conn = OracleJDBC.openConn();
//			ps = conn.prepareStatement(selcetSQL);
//				ps.setString(1, userid);
//			rs = ps.executeQuery();
//			
//			while(rs.next()){
//				 m = new MemberVO(rs.getString(1),rs.getString(2), 
//									null, rs.getString(3), null,rs.getString(4));
//				
//				}
//			}catch(Exception ex){
//				ex.printStackTrace();
//			}finally{
//				OracleJDBC.closeConn(conn, ps, rs);}
//		return m;
//	}
	
}
